***** MCMIS Catalog **************************************
***** Yuanning Liang *************************************


/* Clean Data */
local rawdatafolder "`workfolder'/FULLDATA/SFTPCornell"
local cleandatafolder "`workfolder'/FULLDATA/CleanData"
cd "`cleandatafolder'"


/* 1. Carrier Census 20181008 */
import delimited "`workfolder'/FULLDATA/SFTPCornell/CENSUS_PUB_20181008wHeader.txt", delimiter("~") encoding(ISO-8859-1)
	// 1.7mil carriers
local lab_var = 1
if `lab_var' == 1 {	 // label variables
label var	acc_rate "RECORDABLE ACCIDENT RATE "
label var	act_stat "STATUS "
label var	addcode "REASON CODE / ADD "
label var	adddate "DATE ADDED "
label var	adduserid "ADD USER ID "
label var	avg_tld "# DRIVERS / AVG NUMBER TRIP  "
label var	beverages "CARGO TRANSPORTED "
label var	bldgmat "CARGO TRANSPORTED "
label var	cargoothr "CARGO TRANSPORTED "
label var	carship "ENTITY TYPE "
label var	cdl_drs "# DRIVERS / TOTAL WITH CDL"
label var	cell_num "CELL PHONE NUMBER "
label var	dot_number "USDOT NUMBER "
label var	chem "CARGO TRANSPORTED "
label var	chgndate "DATE LAST CHANGED "
label var	class "CLASSIFICATION "
label var	classdef "CLASSIFICATION / OTHER DEFINED "
label var	coalcoke "CARGO TRANSPORTED  "
label var	coldfood "CARGO TRANSPORTED "
label var	company_rep1 "COMPANY REPRESENTATIVE ONE "
label var	company_rep2 "COMPANY REPRESENTATIVE TWO "
label var	construct "CARGO TRANSPORTED "
label var	createdate "CREATION DATE "
label var	crrhmintra "OPERATION / CARRIER / INTRASTATE / HAZMAT "
label var	crrinter "OPERATION / CARRIER / INTERSTATE "
label var	crrintra "OPERATION / CARRIER / INTRASTATE / NON-HAZMAT "
label var	dbnum "DUN & BRADSTREET NUMBER "
label var	delcode "REASON CODE / DELETE "
label var	deldate "DATE DELETED "
label var	deluserid "DELETE USER ID "
label var	drivetow "CARGO TRANSPORTED  "
label var	drybulk "CARGO TRANSPORTED "
label var	emailaddress "EMAIL ADDRESS "
label var	farmsupp "CARGO TRANSPORTED "
label var	fax_num "OFFICE FAX PHONE NUMBER "
label var	fleetsize "FLEET SIZE CODE "
label var	garbage "CARGO TRANSPORTED  "
label var	genfreight "CARGO TRANSPORTED  "
label var	grainfeed "CARGO TRANSPORTED "
label var	hm_ind "HAZMAT INDICATOR "
label var	household "CARGO TRANSPORTED  "
label var	icc1 "ICC DOCKET NUMBER / FIRST "
label var	icc2 "ICC DOCKET NUMBER / SECOND "
label var	icc3 "ICC DOCKET NUMBER / THIRD "
label var	icc_docket_1_prefix "ICC DOCKET NUMBER-1-PREFIX "
label var	icc_docket_2_prefix "ICC DOCKET-2-PREFIX "
label var	icc_docket_3_prefix "ICC DOCKET-3-PREFIX "
label var	inter_drs "# DRIVERS / INTERSTATE TOTAL "
label var	intergt100 "# DRIVERS / INTERSTATE BEYOND 100 MILES "
label var	interlt100 "# DRIVERS / INTERSTATE WITHIN 100 MILES "
label var	intermodal "CARGO TRANSPORTED  "
label var	intra_drs "# DRIVERS / INTRASTATE TOTAL "
label var	intragt100 "# DRIVERS / INTRASTATE BEYOND 100 MILES"
label var	intralt100 "# DRIVERS / INTRASTATE WITHIN 100 MILES  "
label var	liqgas "CARGO TRANSPORTED  "
label var	livestock "CARGO TRANSPORTED  "
label var	logpole "CARGO TRANSPORTED "
label var	machlrg "CARGO TRANSPORTED  "
label var	mai_barrio "MEXICAN NEIGHBORHOOD / MAILING "
label var	mai_city "MAILING ADDRESS / CITY "
label var	mai_cnty "MAILING ADDRESS / COUNTY CODE "
label var	mai_natn "MAILING ADDRESS / NATIONALITY "
label var	mai_st "MAILING ADDRESS / STATE CODE "
label var	mai_str "MAILING ADDRESS / STREET "
label var	mai_zip "MAILING ADDRESS / ZIP CODE "
label var	mcs_150_date "MCS-150 DATE "
label var	mcs150 "MCS-150 MILEAGE YEAR "
label var	mcsipdate "MCSIP DATE "
label var	mcsipstep "MCSIP STEP "
label var	meat "CARGO TRANSPORTED  "
label var	metalsheet "CARGO TRANSPORTED  "
label var	mcs150mileageyear "MCS-150 MILEAGE YEAR "
label var	mlg151 "REVIEW MILEAGE/ MCS-151 "
label var	mlg150 "MILEAGE / CALENDAR YEAR/ MCS-150 "
label var	mobilehome "CARGO TRANSPORTED "
label var	motorveh "CARGO TRANSPORTED "
label var	name "LEGAL NAME "
label var	name_dba "DOING BUSINESS AS NAME "
label var	oic "OFFICER IN CHARGE (OIC) CODE "
label var	oilfield "CARGO TRANSPORTED "
label var	org "BUSINESS ORGANIZATION / CODE "
label var	othercargo "CARGO TRANSPORTED  "
label var	ownbus_16 "# EQUIPMENT UNITS OWNED "
label var	owncoach "# EQUIPMENT UNITS OWNED "
label var	ownlimo_1_8 "# EQUIPMENT UNITS OWNED "
label var	ownlimo_16 "# EQUIPMENT UNITS OWNED "
label var	ownlimo_9_15 "# EQUIPMENT UNITS OWNED "
label var	ownschool_1_8 "# EQUIPMENT UNITS OWNED "
label var	ownschool_16 "# EQUIPMENT UNITS OWNED "
label var	ownschool_9_15 "# EQUIPMENT UNITS OWNED "
label var	owntract "# EQUIPMENT UNITS OWNED "
label var	owntrail "# EQUIPMENT UNITS OWNED "
label var	owntruck "# EQUIPMENT UNITS OWNED "
label var	ownvan_1_8 "# EQUIPMENT UNITS OWNED "
label var	ownvan_9_15 "# EQUIPMENT UNITS OWNED "
label var	paperprod "CARGO TRANSPORTED "
label var	passengers "CARGO TRANSPORTED  "
label var	phy_barrio "MEXICAN NEIGHBORHOOD / PHYSICAL "
label var	phy_city "PHYSICAL ADDRESS / CITY "
label var	phy_cnty "PHYSICAL ADDRESS / COUNTY CODE "
label var	phy_natn "PHYSICAL ADDRESS / NATIONALITY "
label var	phy_st "PHYSICAL ADDRESS/STATE CODE "
label var	phy_str "PHYSICAL ADDRESS / STREET "
label var	phy_zip "PHYSICAL ADDRESS / ZIP CODE "
label var	produce "CARGO TRANSPORTED  "
label var	ratedate "SAFETY RATING / EFFECTIVE DATE "
label var	rating "SAFETY RATING / TYPE CODE "
label var	rec_ "RECORD UPDATED FLAG "
label var	reg "PHYSICAL ADDRESS / FMCSA REGION "
label var	repprevrat "PREVENTABLE RECORDABLE ACCIDENT RATE "
label var	revdate "LATEST REVIEW / DATE "
label var	revdocnum "LATEST REVIEW / DOCUMENT # "
label var	revtype "LATEST REVIEW / TYPE "
label var	shpinter "OPERATION / SHIPPER / INTERSTATE "
label var	shpintra "OPERATION / SHIPPER / INTRASTATE "
label var	tel_num "OFFICE TELEPHONE NUMBER "
label var	terr "SAFETY INVESTIGATOR TERRITORY CODE "
label var	tot_buses "TOTAL NUMBER OF BUSES "
label var	tot_cars "TOTAL CARS "
label var	tot_drs "# DRIVERS / GRAND TOTAL "
label var	tot_pwr "TOTAL NUMBER OF POWER UNITS "
label var	tot_trucks "TOTAL NUMBER OF TRUCKS "
label var	trmbus_16 "# EQUIPMENT UNITS TERM LEASED "
label var	trmcoach "# EQUIPMENT UNITS TERM LEASED "
label var	trmlimo_1_8 "# EQUIPMENT UNITS TERM LEASED "
label var	trmlimo_16 "# EQUIPMENT UNITS TERM LEASED "
label var	trmlimo_9_15 "# EQUIPMENT UNITS TERM LEASED "
label var	trmschool_1_8 "# EQUIPMENT UNITS TERM LEASED "
label var	trmschool_16 "# EQUIPMENT UNITS TERM LEASED "
label var	trmschool_9_15 "# EQUIPMENT UNITS TERM LEASED "
label var	trmtract "# EQUIPMENT UNITS TERM LEASED "
label var	trmtrail "# EQUIPMENT UNITS TERM LEASED "
label var	trmtruck "# EQUIPMENT UNITS TERM LEASED "
label var	trmvan_1_8 "# EQUIPMENT UNITS TERM LEASED "
label var	trmvan_9_15 "# EQUIPMENT UNITS TERM LEASED "
label var	trpbus_16 "# EQUIPMENT UNITS TRIP LEASED "
label var	trpcoach "# EQUIPMENT UNITS TRIP LEASED "
label var	trplimo_1_8 "# EQUIPMENT UNITS TRIP LEASED "
label var	trplimo_16 "# EQUIPMENT UNITS TRIP LEASED "
label var	trplimo_9_15 "# EQUIPMENT UNITS TRIP LEASED "
label var	trpschool_1_8 "# EQUIPMENT UNITS TRIP LEASED "
label var	trpschool_16 "# EQUIPMENT UNITS TRIP LEASED "
label var	trpschool_9_15 "# EQUIPMENT UNITS TRIP LEASED "
label var	trptract "# EQUIPMENT UNITS TRIP LEASED "
label var	trptrail "# EQUIPMENT UNITS TRIP LEASED "
label var	trptruck "# EQUIPMENT UNITS TRIP LEASED "
label var	trpvan_1_8 "# EQUIPMENT UNITS TRIP LEASED "
label var	trpvan_9_15 "# EQUIPMENT UNITS TRIP LEASED "
label var	undeliv_mai "UNDELIVERABLE MAILING ADDRESS "
label var	undeliv_phy "UNDELIVERABLE PHYSICAL ADDRESS "
label var	upd_reas "REASON CODE / CHANGE "
label var	rec_update_flag "RECORD UPDATED FLAG "
label var	usdot_revoked_flag "USDOT REVOKED FLAG "
label var	usdot_revoked_number "USDOT REVOKED NUMBER "
label var	userid "USER ID "
label var	usmail "CARGO TRANSPORTED  "
label var	utility "CARGO TRANSPORTED "
label var	vehicle_registrant "OPERATION / VEHICLE REGISTRANT "
label var	version "VERSION "
label var	waterwell "CARGO TRANSPORTED "
}
save carrier_census_18,replace
	// refer to dictionary: Census File Dictionary.xlsx
	


use "CensusData/carrier_census_18" ,clear
*count if act_stat == "A"  // all carrier companies in the file are active

local drop_var = 1   // drop a list of nonrelevant variables in census

if `drop_var' == 1 {
	drop act_stat
	drop company_rep1 company_rep2 emailaddress dbnum tel_num cell_num fax_num icc_docket_1_prefix icc1 icc_docket_2_prefix icc2 icc_docket_3_prefix icc3 mcsipstep mcsipdate createdate version
	drop undeliv_phy mai_natn mai_str mai_city mai_cnty mai_st mai_zip undeliv_mai oic terr
	drop adduserid deluserid delcode upd_reas addcode userid mai_barrio phy_barrio
	drop name name_dba phy_str phy_city usdot_revoked_number usdot_revoked_flag rec_update_flag
	drop trm* trp* own* 
}

* carrier address
keep if phy_natn == "US"
drop phy_natn

	//extract 5-digit zipcode 
gen phy_zip5 = substr(phy_zip, 1, 5)
drop phy_zip

rename phy_st state
gen str3 COUNTYFP = string(phy_cnty, "%03.0f")
merge m:1 state COUNTYFP using "CensusData/countyfips", keep(master match) nogen

drop countyfips
egen carrier_countyfips = concat(STATEFP COUNTYFP)

rename state carrier_hq_st
drop STATEFP COUNTYFP

* Entry time: Based on FMCSA schedule of updating mcs150 form to find operating companies
replace mcs_150_date = chgndate if mcs_150_date == .
replace mcs_150_date = adddate if mcs_150_date == .

gen mcs150_year = floor(mcs_150_date/10000)
gen mcs150_month = floor((mcs_150_date - mcs150_year*10000)/100)
gen mcs150_day = mcs_150_date - mcs150_year * 10000 - mcs150_month * 100
gen mcs150_date = mdy(mcs150_month,mcs150_day,mcs150_year)

gen add_year = floor(adddate/10000)
gen add_month = floor((adddate - add_year*10000)/100)
gen add_day = adddate - add_year * 10000 - add_month * 100
gen add_date = mdy(add_month,add_day,add_year)

* addtime is the entry time
gen addtime = add_date if add_date <= mcs150_date & mcs150_date ~=.  // select the earlier time of adddate and mcs_150_date as the entry time
replace addtime = mcs150_date if add_date > mcs150_date & mcs150_date ~=.
replace addtime = add_date if mcs150_date == .
gen addtime_year = year(addtime)
gen addtime_month = month(addtime)

drop mcs150_year mcs150_month mcs150_day mcs150_date add_year add_month add_day add_date


compress
save "CensusData/carrier_census" ,replace



/* 2. Crash File
 	refer to dictionary: Crash File Dictionary.xlsx */

local rawdatafolder "`workfolder'/FULLDATA/SFTPCornell"

local rawdatafolder "`workfolder'/FULLDATA/SFTPCornell"
cd "`rawdatafolder'"

/* A. Crash Master Files */

local rawdatafolder "`workfolder'/FULLDATA/SFTPCornell"
local cleandatafolder "`workfolder'/FULLDATA/CleanData"

cd "`cleandatafolder'"
local crashfile "`rawdatafolder'/Crash_"

local name_var = 1
local lab_var = 1
import delimited "`crashfile'2018/CrashMaster_01012018_10082018.txt", encoding(ISO-8859-1) clear
if `name_var' == 1{	  // name variables
local crash_var "CRASH_ID	REPORT_STATE	REPORT_NUMBER	REPORT_DATE	REPORT_TIME	REPORT_SEQ_NO	DOT_NUMBER	CI_STATUS_CD	FINAL_STATUS_DATE	LOCATION	CITY_CODE	CITY	STATE	COUNTY_CODE	TRUCK_BUS_IND	TRAFFICWAY_ID	ACCESS_CONTROL_ID	ROAD_SURFACE_CONDITION_ID	CARGO_BODY_TYPE_ID	GVW_RATING_ID	VEHICLE_IDENTIFICATION_NUMBER	VEHICLE_LICENSE_NUMBER	VEHICLE_LIC_STATE	VEHICLE_HAZMAT_PLACARD	WEATHER_CONDITION_ID	VEHICLE_CONFIGURATION_ID	LIGHT_CONDITION_ID	HAZMAT_RELEASED	AGENCY	OFFICER_BADGE	VEHICLES_IN_ACCIDENT	FATALITIES	INJURIES	TOW_AWAY	FEDERAL_RECORDABLE	STATE_RECORDABLE	SNET_VERSION_NUMBER	SNET_SEQUENCE_ID	TRANSACTION_CODE	TRANSACTION_DATE	UPLOAD_FIRST_BYTE	UPLOAD_DOT_NUMBER	UPLOAD_SEARCH_INDICATOR	UPLOAD_DATE	CENSUS_SEARCH_DATE	ADD_DATE	CHANGE_DATE	CHANGE_BY_USER	CHANGE_BY_APPL	SNET_INPUT_DATE"
local v "v1	v2	v3	v4	v5	v6	v7	v8	v9	v10	v11	v12	v13	v14	v15	v16	v17	v18	v19	v20	v21	v22	v23	v24	v25	v26	v27	v28	v29	v30	v31	v32	v33	v34	v35	v36	v37	v38	v39	v40	v41	v42	v43	v44	v45	v46	v47	v48	v49	v50"
rename (`v') (`crash_var')	
}
if `lab_var' == 1 {	 // label variables
label var 	CRASH_ID  "CRASH ID "
label var	REPORT_STATE  "REPORT STATE "
label var	REPORT_NUMBER  "REPORT NUMBER "
label var	REPORT_DATE  "REPORT DATE "
label var	REPORT_TIME  "REPORT TIME "
label var	REPORT_SEQ_NO  "CRASH SEQUENCE NUMBER "
label var	DOT_NUMBER  "CENSUS NUMBER "
label var	CI_STATUS_CD  "STATUS CODE "
label var	FINAL_STATUS_DATE  "FINAL STATUS DATE "
label var	LOCATION  "LOCATION "
label var	CITY_CODE  "CITY CODE "
label var	CITY  "CITY "
label var	STATE  "STATE "
label var	COUNTY_CODE  "COUNTY CODE "
label var	TRUCK_BUS_IND  "TRUCK/BUS "
label var	TRAFFICWAY_ID  "TRAFFICWAY "
label var	ACCESS_CONTROL_ID  "ROAD ACCESS CONTROL "
label var	ROAD_SURFACE_CONDITION_ID  "ROAD SURFACE CONDITION "
label var	CARGO_BODY_TYPE_ID  "CARGO BODY TYPE "
label var	GVW_RATING_ID  "GROSS VEHICLE WEIGHT RATING ID "
label var	VEHICLE_IDENTIFICATION_NUMBER  "VEHICLE IDENTIFICATION NUMBER (VIN) "
label var	VEHICLE_LICENSE_NUMBER  "VEHICLE LICENSE NUMBER "
label var	VEHICLE_LIC_STATE  "VEHICLE LICENSE STATE "
label var	VEHICLE_HAZMAT_PLACARD  "VEHICLE HAZMAT PLACARD "
label var	WEATHER_CONDITION_ID  "WEATHER CONDITION "
label var	VEHICLE_CONFIGURATION_ID  "VEHICLE CONFIGURATION "
label var	LIGHT_CONDITION_ID  "LIGHT CONDITION "
label var	HAZMAT_RELEASED  "RELEASE OF HAZARDOUS MATERIALS CARGO "
label var	AGENCY  "REPORTING AGENCY "
label var	OFFICER_BADGE  "OFFICER BADGE NUMBER "
label var	VEHICLES_IN_ACCIDENT  "VEHICLES IN ACCIDENT "
label var	FATALITIES  "NUMBER OF FATALITIES "
label var	INJURIES  "NUMBER OF INJURIES "
label var	TOW_AWAY  "TOW AWAY "
label var	FEDERAL_RECORDABLE  "FEDERALLY RECORDABLE "
label var	STATE_RECORDABLE  "STATE RECORDABLE "
label var	SNET_VERSION_NUMBER  "SAFETYNET VERSION NUMBER "
label var	SNET_SEQUENCE_ID  "SAFETYNET SEQUENCE ID "
label var	TRANSACTION_CODE  "TRANSACTION CODE "
label var	TRANSACTION_DATE  "TRANSACTION DATE "
label var	UPLOAD_FIRST_BYTE  "UPLOAD CENSUS FIRST BYTE "
label var	UPLOAD_DOT_NUMBER  "UPLOAD USDOT NUMBER "
label var	UPLOAD_SEARCH_INDICATOR  "UPLOAD SEARCH INDICATOR "
label var	UPLOAD_DATE  "DATE UPLOADED "
label var	CENSUS_SEARCH_DATE  "CENSUS SEARCH DATE "
label var	ADD_DATE  "ADD DATE "
label var	CHANGE_DATE  "LAST CHANGE DATE "
label var	CHANGE_BY_USER  "CHANGED BY USER "
label var	CHANGE_BY_APPL  "CHANGED BY APPLICATION "
label var	SNET_INPUT_DATE  "SAFETYNET INPUT DATE " 
}
export delimited using "CrashData/CrashMaster_2018.csv", replace    // dta is twice as large
forvalues i = 1989/2017 {
import delimited "`crashfile'`i'/CrashMaster_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
if `name_var' == 1{	  
local crash_var "CRASH_ID	REPORT_STATE	REPORT_NUMBER	REPORT_DATE	REPORT_TIME	REPORT_SEQ_NO	DOT_NUMBER	CI_STATUS_CD	FINAL_STATUS_DATE	LOCATION	CITY_CODE	CITY	STATE	COUNTY_CODE	TRUCK_BUS_IND	TRAFFICWAY_ID	ACCESS_CONTROL_ID	ROAD_SURFACE_CONDITION_ID	CARGO_BODY_TYPE_ID	GVW_RATING_ID	VEHICLE_IDENTIFICATION_NUMBER	VEHICLE_LICENSE_NUMBER	VEHICLE_LIC_STATE	VEHICLE_HAZMAT_PLACARD	WEATHER_CONDITION_ID	VEHICLE_CONFIGURATION_ID	LIGHT_CONDITION_ID	HAZMAT_RELEASED	AGENCY	OFFICER_BADGE	VEHICLES_IN_ACCIDENT	FATALITIES	INJURIES	TOW_AWAY	FEDERAL_RECORDABLE	STATE_RECORDABLE	SNET_VERSION_NUMBER	SNET_SEQUENCE_ID	TRANSACTION_CODE	TRANSACTION_DATE	UPLOAD_FIRST_BYTE	UPLOAD_DOT_NUMBER	UPLOAD_SEARCH_INDICATOR	UPLOAD_DATE	CENSUS_SEARCH_DATE	ADD_DATE	CHANGE_DATE	CHANGE_BY_USER	CHANGE_BY_APPL	SNET_INPUT_DATE"
local v "v1	v2	v3	v4	v5	v6	v7	v8	v9	v10	v11	v12	v13	v14	v15	v16	v17	v18	v19	v20	v21	v22	v23	v24	v25	v26	v27	v28	v29	v30	v31	v32	v33	v34	v35	v36	v37	v38	v39	v40	v41	v42	v43	v44	v45	v46	v47	v48	v49	v50"
rename (`v') (`crash_var')	
}
if `lab_var' == 1 {	 
label var 	CRASH_ID  "CRASH ID "
label var	REPORT_STATE  "REPORT STATE "
label var	REPORT_NUMBER  "REPORT NUMBER "
label var	REPORT_DATE  "REPORT DATE "
label var	REPORT_TIME  "REPORT TIME "
label var	REPORT_SEQ_NO  "CRASH SEQUENCE NUMBER "
label var	DOT_NUMBER  "CENSUS NUMBER "
label var	CI_STATUS_CD  "STATUS CODE "
label var	FINAL_STATUS_DATE  "FINAL STATUS DATE "
label var	LOCATION  "LOCATION "
label var	CITY_CODE  "CITY CODE "
label var	CITY  "CITY "
label var	STATE  "STATE "
label var	COUNTY_CODE  "COUNTY CODE "
label var	TRUCK_BUS_IND  "TRUCK/BUS "
label var	TRAFFICWAY_ID  "TRAFFICWAY "
label var	ACCESS_CONTROL_ID  "ROAD ACCESS CONTROL "
label var	ROAD_SURFACE_CONDITION_ID  "ROAD SURFACE CONDITION "
label var	CARGO_BODY_TYPE_ID  "CARGO BODY TYPE "
label var	GVW_RATING_ID  "GROSS VEHICLE WEIGHT RATING ID "
label var	VEHICLE_IDENTIFICATION_NUMBER  "VEHICLE IDENTIFICATION NUMBER (VIN) "
label var	VEHICLE_LICENSE_NUMBER  "VEHICLE LICENSE NUMBER "
label var	VEHICLE_LIC_STATE  "VEHICLE LICENSE STATE "
label var	VEHICLE_HAZMAT_PLACARD  "VEHICLE HAZMAT PLACARD "
label var	WEATHER_CONDITION_ID  "WEATHER CONDITION "
label var	VEHICLE_CONFIGURATION_ID  "VEHICLE CONFIGURATION "
label var	LIGHT_CONDITION_ID  "LIGHT CONDITION "
label var	HAZMAT_RELEASED  "RELEASE OF HAZARDOUS MATERIALS CARGO "
label var	AGENCY  "REPORTING AGENCY "
label var	OFFICER_BADGE  "OFFICER BADGE NUMBER "
label var	VEHICLES_IN_ACCIDENT  "VEHICLES IN ACCIDENT "
label var	FATALITIES  "NUMBER OF FATALITIES "
label var	INJURIES  "NUMBER OF INJURIES "
label var	TOW_AWAY  "TOW AWAY "
label var	FEDERAL_RECORDABLE  "FEDERALLY RECORDABLE "
label var	STATE_RECORDABLE  "STATE RECORDABLE "
label var	SNET_VERSION_NUMBER  "SAFETYNET VERSION NUMBER "
label var	SNET_SEQUENCE_ID  "SAFETYNET SEQUENCE ID "
label var	TRANSACTION_CODE  "TRANSACTION CODE "
label var	TRANSACTION_DATE  "TRANSACTION DATE "
label var	UPLOAD_FIRST_BYTE  "UPLOAD CENSUS FIRST BYTE "
label var	UPLOAD_DOT_NUMBER  "UPLOAD USDOT NUMBER "
label var	UPLOAD_SEARCH_INDICATOR  "UPLOAD SEARCH INDICATOR "
label var	UPLOAD_DATE  "DATE UPLOADED "
label var	CENSUS_SEARCH_DATE  "CENSUS SEARCH DATE "
label var	ADD_DATE  "ADD DATE "
label var	CHANGE_DATE  "LAST CHANGE DATE "
label var	CHANGE_BY_USER  "CHANGED BY USER "
label var	CHANGE_BY_APPL  "CHANGED BY APPLICATION "
label var	SNET_INPUT_DATE  "SAFETYNET INPUT DATE " 
}
export delimited using "CrashData/CrashMaster_`i'.csv", replace
}




/* B. Crash Carrier File */
local rawdatafolder "`workfolder'/FULLDATA/SFTPCornell"
local cleandatafolder "`workfolder'/FULLDATA/CleanData"

cd "`cleandatafolder'"
local crashfile "`rawdatafolder'/Crash_"

local name_var = 1
local lab_var = 1
import delimited "`crashfile'2018/CrashCarrier_01012018_10082018.txt", encoding(ISO-8859-1) clear
if `name_var' == 1{ 
local crash_var "CRASH_CARRIER_ID	CRASH_ID	CARRIER_SOURCE_CODE	CRASH_CARRIER_NAME	CRASH_CARRIER_STREET	CRASH_CARRIER_CITY	CRASH_CARRIER_CITY_CODE	CRASH_CARRIER_STATE	CRASH_CARRIER_ZIP_CODE	CRASH_COLONIA	PREFIX	DOCKET_NUMBER	CRASH_CARRIER_INTERSTATE	NO_ID_FLAG	STATE_NUMBER	STATE_ISSUING_NUMBER"
local v "v1	v2	v3	v4	v5	v6	v7	v8	v9	v10	v11	v12	v13	v14	v15	v16"
rename (`v') (`crash_var')	
}
if `lab_var' == 1 {	 
label var 	CRASH_CARRIER_ID	 "CRASH CARRIER ID"
label var	CRASH_ID	 "CRASH ID"
label var	CARRIER_SOURCE_CODE	 "CARRIER NAME SOURCE"
label var	CRASH_CARRIER_NAME	 "CARRIER NAME"
label var	CRASH_CARRIER_STREET	 "CARRIER ADDRESS/STREET"
label var	CRASH_CARRIER_CITY	 "CARRIER ADDRESS/CITY NAME"
label var	CRASH_CARRIER_CITY_CODE	 "CARRIER ADDRESS/CITY CODE"
label var	CRASH_CARRIER_STATE	 "CARRIER ADDRESS/STATE"
label var	CRASH_CARRIER_ZIP_CODE	 "CARRIER ADDRESS/ZIP CODE"
label var	CRASH_COLONIA	 "CARRIER ADDRESS/MEXICAN COLONIA"
label var	PREFIX	 "PREFIX"
label var	DOCKET_NUMBER	 "DOCKET NUMBER"
label var	CRASH_CARRIER_INTERSTATE	 "INTERSTATE"
label var	NO_ID_FLAG	 "NO CENSUS NUMBER"
label var	STATE_NUMBER	 "STATE CENSUS NUMBER"
label var	STATE_ISSUING_NUMBER	 "STATE ISSUING STATE CENSUS NUMBER" 
}

export delimited using "CrashData/CrashCarrier_2018.csv", replace
forvalues i = 1989/2017 {
import delimited "`crashfile'`i'/CrashCarrier_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
if `name_var' == 1{
local crash_var "CRASH_CARRIER_ID	CRASH_ID	CARRIER_SOURCE_CODE	CRASH_CARRIER_NAME	CRASH_CARRIER_STREET	CRASH_CARRIER_CITY	CRASH_CARRIER_CITY_CODE	CRASH_CARRIER_STATE	CRASH_CARRIER_ZIP_CODE	CRASH_COLONIA	PREFIX	DOCKET_NUMBER	CRASH_CARRIER_INTERSTATE	NO_ID_FLAG	STATE_NUMBER	STATE_ISSUING_NUMBER"
local v "v1	v2	v3	v4	v5	v6	v7	v8	v9	v10	v11	v12	v13	v14	v15	v16"
rename (`v') (`crash_var')	
}
if `lab_var' == 1 {
label var 	CRASH_CARRIER_ID	 "CRASH CARRIER ID"
label var	CRASH_ID	 "CRASH ID"
label var	CARRIER_SOURCE_CODE	 "CARRIER NAME SOURCE"
label var	CRASH_CARRIER_NAME	 "CARRIER NAME"
label var	CRASH_CARRIER_STREET	 "CARRIER ADDRESS/STREET"
label var	CRASH_CARRIER_CITY	 "CARRIER ADDRESS/CITY NAME"
label var	CRASH_CARRIER_CITY_CODE	 "CARRIER ADDRESS/CITY CODE"
label var	CRASH_CARRIER_STATE	 "CARRIER ADDRESS/STATE"
label var	CRASH_CARRIER_ZIP_CODE	 "CARRIER ADDRESS/ZIP CODE"
label var	CRASH_COLONIA	 "CARRIER ADDRESS/MEXICAN COLONIA"
label var	PREFIX	 "PREFIX"
label var	DOCKET_NUMBER	 "DOCKET NUMBER"
label var	CRASH_CARRIER_INTERSTATE	 "INTERSTATE"
label var	NO_ID_FLAG	 "NO CENSUS NUMBER"
label var	STATE_NUMBER	 "STATE CENSUS NUMBER"
label var	STATE_ISSUING_NUMBER	 "STATE ISSUING STATE CENSUS NUMBER" 
}

export delimited using "CrashData/CrashCarrier_`i'.csv", replace
}






/* C. Crash Event File */
local rawdatafolder "`workfolder'/FULLDATA/SFTPCornell"
local cleandatafolder "`workfolder'/FULLDATA/CleanData"

cd "`cleandatafolder'"
local crashfile "`rawdatafolder'/Crash_"

local name_var = 1
local lab_var = 1
import delimited "`crashfile'2018/CrashEvent_01012018_10082018.txt", encoding(ISO-8859-1) clear
if `name_var' == 1 { 
local crash_var "CRASH_EVENT_ID CRASH_ID SEQ_NO EVENT_ID EVENT_OTHER_DESC"
local v "v1	v2	v3	v4	v5"
rename (`v') (`crash_var')	
}
if `lab_var' == 1 {	 
label var CRASH_EVENT_ID "CRASH EVENT ID"
label var CRASH_ID "CRASH ID"
label var SEQ_NO "SEQUENCE NUMBER"
label var EVENT_ID "EVENT ID"
label var EVENT_OTHER_DESC "OTHER DECSRIPTION"
}


export delimited using "CrashData/CrashEvent_2018.csv", replace
forvalues i = 1989/2017 {
import delimited "`crashfile'`i'/CrashEvent_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear

if `name_var' == 1{ 
local crash_var "CRASH_EVENT_ID CRASH_ID SEQ_NO EVENT_ID EVENT_OTHER_DESC"
local v "v1	v2	v3	v4	v5"
rename (`v') (`crash_var')	
}
if `lab_var' == 1 {	 
label var CRASH_EVENT_ID "CRASH EVENT ID"
label var CRASH_ID "CRASH ID"
label var SEQ_NO "SEQUENCE NUMBER"
label var EVENT_ID "EVENT ID"
label var EVENT_OTHER_DESC "OTHER DECSRIPTION" 
}
export delimited using "CrashData/CrashEvent_`i'.csv", replace
}












/* 3. Inspection File
 	refer to dictionary: Inspection File Dictionary.xlsx */

local rawdatafolder "`workfolder'/FULLDATA/SFTPCornell"
local cleandatafolder "`workfolder'/FULLDATA/CleanData"

cd "`cleandatafolder'"
local inspfile "`rawdatafolder'/Insp_"

// all tables (8 tables) 
local name_var = 1
local lab_var = 1

****************
local t = "Insp_Pub"	
	// inspection file in 2018
	import delimited "`inspfile'2018/`t'_01012018_10082018.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   // (69 variables, ** are not publicly available)
	local insp_var "INSPECTION_ID	DOT_NUMBER	REPORT_STATE	REPORT_NUMBER	INSP_DATE	INSP_START_TIME	INSP_END_TIME	REGISTRATION_DATE	REGION	CI_STATUS_CODE	SAFETY_INSPECTOR_KEY	LOCATION	LOCATION_DESC	COUNTY_CODE_STATE	COUNTY_CODE	INSP_LEVEL_ID	SERVICE_CENTER	CENSUS_SOURCE_ID	INSP_FACILITY	SHIPPER_NAME	SHIPPING_PAPER_NUMBER	CARGO_TANK	HAZMAT_PLACARD_REQ	ASPEN_VERSION_NUMBER	SNET_VERSION_NUMBER	SNET_SEARCH_DATE	ALCOHOL_CONTROL_SUB	DRUG_INTRDCTN_SEARCH	DRUG_INTRDCTN_ARRESTS	SIZE_WEIGHT_ENF	TRAFFIC_ENF	LOCAL_ENF_JURISDICTION	INSP_CONFIDENCE_LEVEL	PEN_CEN_MATCH	PENFIELD2	PENFIELD3	FINAL_STATUS_DATE	POST_ACC_IND	GROSS_COMB_VEH_WT	DEFECT_VER	OOS_DEFECT_VER	VIOL_NOT_SENT	OOS_NOT_SENT	VIOL_TOTAL	OOS_TOTAL	DRIVER_VIOL_TOTAL	DRIVER_OOS_TOTAL	VEHICLE_VIOL_TOTAL	VEHICLE_OOS_TOTAL	HAZMAT_VIOL_TOTAL	HAZMAT_OOS_TOTAL	SNET_SEQUENCE_ID	ORIG_REPORT_STATE	ORIG_REPORT_NUMBER	ORIG_REPORT_DATE	ORIG_REPORT_TIME	TRANSACTION_CODE	TRANSACTION_DATE	UPLOAD_DATE	UPLOAD_FIRST_BYTE	UPLOAD_DOT_NUMBER	UPLOAD_SEARCH_INDICATOR	CENSUS_SEARCH_DATE	MCMIS_ADD_DATE	CHANGE_BY_USER	CHANGE_DATE	CHANGE_BY_APPL	SNET_INPUT_DATE	SOURCE_OFFICE"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11	v12	v13	v14	v15	v16	v17	v18	v19	v20	v21	v22	v23	v24	v25	v26	v27	v28	v29	v30	v31	v32	v33	v34	v35	v36	v37	v38	v39	v40	v41	v42	v43	v44	v45	v46	v47	v48	v49	v50	v51	v52	v53	v54	v55	v56	v57	v58	v59	v60	v61	v62	v63	v64	v65	v66	v67	v68	v69"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	
	label var 	INSPECTION_ID "INSPECTION ID "
	label var	DOT_NUMBER "USDOT NUMBER "
	label var	REPORT_STATE "REPORT STATE "
	label var	REPORT_NUMBER "REPORT NUMBER "
	label var	INSP_DATE "INSPECTION DATE "
	label var	INSP_START_TIME "START TIME "
	label var	INSP_END_TIME "END TIME "
	label var	REGISTRATION_DATE "REGISTRATION DATE "
	label var	REGION "REGION "
	label var	CI_STATUS_CODE "STATUS CODE "
	label var	SAFETY_INSPECTOR_KEY "SAFETY INSPECTOR KEY "
	label var	LOCATION "LOCATION "
	label var	LOCATION_DESC "LOCATION DESCRIPTION "
	label var	COUNTY_CODE_STATE "COUNTY CODE STATE "
	label var	COUNTY_CODE "COUNTY CODE "
	label var	INSP_LEVEL_ID "INSPECTION LEVEL ID "
	label var	SERVICE_CENTER "SERVICE CENTER "
	label var	CENSUS_SOURCE_ID "CENSUS SOURCE ID "
	label var	INSP_FACILITY "INSPECTION FACILITY "
	label var	SHIPPER_NAME "SHIPPER NAME "
	label var	SHIPPING_PAPER_NUMBER "SHIPPING PAPER NUMBER "
	label var	CARGO_TANK "CARGO TANK "
	*label var	**HAZMAT_INSP_TYPE "HAZMAT INSPECTION TYPE "
	label var	HAZMAT_PLACARD_REQ "HAZMAT PLACARD REQUIRED "
	label var	ASPEN_VERSION_NUMBER "ASPEN VERSION NUMBER "
	label var	SNET_VERSION_NUMBER "SNET VERSION NUMBER "
	label var	SNET_SEARCH_DATE "SNET SEARCH DATE "
	label var	ALCOHOL_CONTROL_SUB "ALCOHOL/CONTROLLED SUBSTANCE CHECK "
	label var	DRUG_INTRDCTN_SEARCH "DRUG INTERDICTION SEARCH "
	label var	DRUG_INTRDCTN_ARRESTS "DRUG INTERDICTION ARREST "
	label var	SIZE_WEIGHT_ENF "SIZE WEIGHT ENFORCEMENT "
	label var	TRAFFIC_ENF "TRAFFIC ENFORCEMENT "
	label var	LOCAL_ENF_JURISDICTION "LOCAL ENFORCEMENT JURISDICTION "
	label var	INSP_CONFIDENCE_LEVEL "INSPECTOR CONFIDENCE LEVEL "
	label var	PEN_CEN_MATCH "PEN CEN MATCH "
	label var	PENFIELD2 "PEN FIELD 2 "
	label var	PENFIELD3 "PEN FIELD 3 "
	label var	FINAL_STATUS_DATE "FINAL STATUS DATE "
	label var	POST_ACC_IND "POST ACCIDENT INDICATOR "
	label var	GROSS_COMB_VEH_WT "GROSS COMBINED VEHICLE WEIGHT "
	label var	DEFECT_VER "DEFECT VERIFICATION "
	label var	OOS_DEFECT_VER "OUT-OF-SERVICE DEFECT VERIFICATION "
	*label var	**TOTAL_HAZMAT_SENT "TOTAL HAZMAT SENT "
	label var	VIOL_NOT_SENT "VIOLATIONS NOT SENT "
	label var	OOS_NOT_SENT "OUT-OF-SERVICE NOT SENT "
	label var	VIOL_TOTAL "TOTAL VIOLATIONS "
	label var	OOS_TOTAL "TOTAL OUT-OF-SERVICE VIOLATIONS "
	label var	DRIVER_VIOL_TOTAL "TOTAL DRIVER VIOLATIONS "
	label var	DRIVER_OOS_TOTAL "TOTAL DRIVER OUT-OF-SERVICE "
	label var	VEHICLE_VIOL_TOTAL "TOTAL VEHICLE VIOLATIONS "
	label var	VEHICLE_OOS_TOTAL "TOTAL VEHICLE OUT-OF-SERVICE "
	label var	HAZMAT_VIOL_TOTAL "HAZMAT VIOLATIONS TOTAL "
	label var	HAZMAT_OOS_TOTAL "HAZMAT OUT-OF-SERVICE TOTAL "
	label var	SNET_SEQUENCE_ID "SAFETYNET SEQUENCE ID "
	label var	ORIG_REPORT_STATE "ORIGINAL REPORT STATE "
	label var	ORIG_REPORT_NUMBER "ORIGINAL REPORT NUMBER "
	label var	ORIG_REPORT_DATE "ORIGINAL INSPECTION DATE "
	label var	ORIG_REPORT_TIME "ORIGINAL START TIME "
	label var	TRANSACTION_CODE "TRANSACTION CODE "
	label var	TRANSACTION_DATE "TRANSACTION DATE "
	label var	UPLOAD_DATE "UPLOAD DATE "
	label var	UPLOAD_FIRST_BYTE "UPLOAD CENSUS FIRST BYTE "
	label var	UPLOAD_DOT_NUMBER "UPLOAD CENSUS NUMBER "
	label var	UPLOAD_SEARCH_INDICATOR "UPLOAD SEARCH INDICATOR "
	label var	CENSUS_SEARCH_DATE "CENSUS SEARCH DATE "
	label var	MCMIS_ADD_DATE "MCMIS ADD DATE "
	label var	CHANGE_BY_USER "CHANGE BY USER "
	label var	CHANGE_DATE "CHANGE DATE "
	label var	CHANGE_BY_APPL "CHANGE BY APPLICATION "
	label var	SNET_INPUT_DATE "SAFETYNET INPUT DATE "
	label var	SOURCE_OFFICE "SOURCE OFFICE "   
	}	
	
	
	export delimited using "InspectionData/`t'_2018.csv", replace    // dta is twice as large
	// inspection file in all other years
	forvalues i = 2000/2017 {
	import delimited "`inspfile'`i'/`t'_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   
	local insp_var "INSPECTION_ID	DOT_NUMBER	REPORT_STATE	REPORT_NUMBER	INSP_DATE	INSP_START_TIME	INSP_END_TIME	REGISTRATION_DATE	REGION	CI_STATUS_CODE	SAFETY_INSPECTOR_KEY	LOCATION	LOCATION_DESC	COUNTY_CODE_STATE	COUNTY_CODE	INSP_LEVEL_ID	SERVICE_CENTER	CENSUS_SOURCE_ID	INSP_FACILITY	SHIPPER_NAME	SHIPPING_PAPER_NUMBER	CARGO_TANK	HAZMAT_PLACARD_REQ	ASPEN_VERSION_NUMBER	SNET_VERSION_NUMBER	SNET_SEARCH_DATE	ALCOHOL_CONTROL_SUB	DRUG_INTRDCTN_SEARCH	DRUG_INTRDCTN_ARRESTS	SIZE_WEIGHT_ENF	TRAFFIC_ENF	LOCAL_ENF_JURISDICTION	INSP_CONFIDENCE_LEVEL	PEN_CEN_MATCH	PENFIELD2	PENFIELD3	FINAL_STATUS_DATE	POST_ACC_IND	GROSS_COMB_VEH_WT	DEFECT_VER	OOS_DEFECT_VER	VIOL_NOT_SENT	OOS_NOT_SENT	VIOL_TOTAL	OOS_TOTAL	DRIVER_VIOL_TOTAL	DRIVER_OOS_TOTAL	VEHICLE_VIOL_TOTAL	VEHICLE_OOS_TOTAL	HAZMAT_VIOL_TOTAL	HAZMAT_OOS_TOTAL	SNET_SEQUENCE_ID	ORIG_REPORT_STATE	ORIG_REPORT_NUMBER	ORIG_REPORT_DATE	ORIG_REPORT_TIME	TRANSACTION_CODE	TRANSACTION_DATE	UPLOAD_DATE	UPLOAD_FIRST_BYTE	UPLOAD_DOT_NUMBER	UPLOAD_SEARCH_INDICATOR	CENSUS_SEARCH_DATE	MCMIS_ADD_DATE	CHANGE_BY_USER	CHANGE_DATE	CHANGE_BY_APPL	SNET_INPUT_DATE	SOURCE_OFFICE"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11	v12	v13	v14	v15	v16	v17	v18	v19	v20	v21	v22	v23	v24	v25	v26	v27	v28	v29	v30	v31	v32	v33	v34	v35	v36	v37	v38	v39	v40	v41	v42	v43	v44	v45	v46	v47	v48	v49	v50	v51	v52	v53	v54	v55	v56	v57	v58	v59	v60	v61	v62	v63	v64	v65	v66	v67	v68	v69"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var 	INSPECTION_ID "INSPECTION ID "
	label var	DOT_NUMBER "USDOT NUMBER "
	label var	REPORT_STATE "REPORT STATE "
	label var	REPORT_NUMBER "REPORT NUMBER "
	label var	INSP_DATE "INSPECTION DATE "
	label var	INSP_START_TIME "START TIME "
	label var	INSP_END_TIME "END TIME "
	label var	REGISTRATION_DATE "REGISTRATION DATE "
	label var	REGION "REGION "
	label var	CI_STATUS_CODE "STATUS CODE "
	label var	SAFETY_INSPECTOR_KEY "SAFETY INSPECTOR KEY "
	label var	LOCATION "LOCATION "
	label var	LOCATION_DESC "LOCATION DESCRIPTION "
	label var	COUNTY_CODE_STATE "COUNTY CODE STATE "
	label var	COUNTY_CODE "COUNTY CODE "
	label var	INSP_LEVEL_ID "INSPECTION LEVEL ID "
	label var	SERVICE_CENTER "SERVICE CENTER "
	label var	CENSUS_SOURCE_ID "CENSUS SOURCE ID "
	label var	INSP_FACILITY "INSPECTION FACILITY "
	label var	SHIPPER_NAME "SHIPPER NAME "
	label var	SHIPPING_PAPER_NUMBER "SHIPPING PAPER NUMBER "
	label var	CARGO_TANK "CARGO TANK "
	*label var	**HAZMAT_INSP_TYPE "HAZMAT INSPECTION TYPE "
	label var	HAZMAT_PLACARD_REQ "HAZMAT PLACARD REQUIRED "
	label var	ASPEN_VERSION_NUMBER "ASPEN VERSION NUMBER "
	label var	SNET_VERSION_NUMBER "SNET VERSION NUMBER "
	label var	SNET_SEARCH_DATE "SNET SEARCH DATE "
	label var	ALCOHOL_CONTROL_SUB "ALCOHOL/CONTROLLED SUBSTANCE CHECK "
	label var	DRUG_INTRDCTN_SEARCH "DRUG INTERDICTION SEARCH "
	label var	DRUG_INTRDCTN_ARRESTS "DRUG INTERDICTION ARREST "
	label var	SIZE_WEIGHT_ENF "SIZE WEIGHT ENFORCEMENT "
	label var	TRAFFIC_ENF "TRAFFIC ENFORCEMENT "
	label var	LOCAL_ENF_JURISDICTION "LOCAL ENFORCEMENT JURISDICTION "
	label var	INSP_CONFIDENCE_LEVEL "INSPECTOR CONFIDENCE LEVEL "
	label var	PEN_CEN_MATCH "PEN CEN MATCH "
	label var	PENFIELD2 "PEN FIELD 2 "
	label var	PENFIELD3 "PEN FIELD 3 "
	label var	FINAL_STATUS_DATE "FINAL STATUS DATE "
	label var	POST_ACC_IND "POST ACCIDENT INDICATOR "
	label var	GROSS_COMB_VEH_WT "GROSS COMBINED VEHICLE WEIGHT "
	label var	DEFECT_VER "DEFECT VERIFICATION "
	label var	OOS_DEFECT_VER "OUT-OF-SERVICE DEFECT VERIFICATION "
	*label var	**TOTAL_HAZMAT_SENT "TOTAL HAZMAT SENT "
	label var	VIOL_NOT_SENT "VIOLATIONS NOT SENT "
	label var	OOS_NOT_SENT "OUT-OF-SERVICE NOT SENT "
	label var	VIOL_TOTAL "TOTAL VIOLATIONS "
	label var	OOS_TOTAL "TOTAL OUT-OF-SERVICE VIOLATIONS "
	label var	DRIVER_VIOL_TOTAL "TOTAL DRIVER VIOLATIONS "
	label var	DRIVER_OOS_TOTAL "TOTAL DRIVER OUT-OF-SERVICE "
	label var	VEHICLE_VIOL_TOTAL "TOTAL VEHICLE VIOLATIONS "
	label var	VEHICLE_OOS_TOTAL "TOTAL VEHICLE OUT-OF-SERVICE "
	label var	HAZMAT_VIOL_TOTAL "HAZMAT VIOLATIONS TOTAL "
	label var	HAZMAT_OOS_TOTAL "HAZMAT OUT-OF-SERVICE TOTAL "
	label var	SNET_SEQUENCE_ID "SAFETYNET SEQUENCE ID "
	label var	ORIG_REPORT_STATE "ORIGINAL REPORT STATE "
	label var	ORIG_REPORT_NUMBER "ORIGINAL REPORT NUMBER "
	label var	ORIG_REPORT_DATE "ORIGINAL INSPECTION DATE "
	label var	ORIG_REPORT_TIME "ORIGINAL START TIME "
	label var	TRANSACTION_CODE "TRANSACTION CODE "
	label var	TRANSACTION_DATE "TRANSACTION DATE "
	label var	UPLOAD_DATE "UPLOAD DATE "
	label var	UPLOAD_FIRST_BYTE "UPLOAD CENSUS FIRST BYTE "
	label var	UPLOAD_DOT_NUMBER "UPLOAD CENSUS NUMBER "
	label var	UPLOAD_SEARCH_INDICATOR "UPLOAD SEARCH INDICATOR "
	label var	CENSUS_SEARCH_DATE "CENSUS SEARCH DATE "
	label var	MCMIS_ADD_DATE "MCMIS ADD DATE "
	label var	CHANGE_BY_USER "CHANGE BY USER "
	label var	CHANGE_DATE "CHANGE DATE "
	label var	CHANGE_BY_APPL "CHANGE BY APPLICATION "
	label var	SNET_INPUT_DATE "SAFETYNET INPUT DATE "
	label var	SOURCE_OFFICE "SOURCE OFFICE "  
	}		
	export delimited using "InspectionData/`t'_`i'.csv", replace
	} 
	foreach i of numlist 1989/1994 1996/1999 {
	import delimited "`inspfile'`i'/`t'_`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   
	local insp_var "INSPECTION_ID	DOT_NUMBER	REPORT_STATE	REPORT_NUMBER	INSP_DATE	INSP_START_TIME	INSP_END_TIME	REGISTRATION_DATE	REGION	CI_STATUS_CODE	SAFETY_INSPECTOR_KEY	LOCATION	LOCATION_DESC	COUNTY_CODE_STATE	COUNTY_CODE	INSP_LEVEL_ID	SERVICE_CENTER	CENSUS_SOURCE_ID	INSP_FACILITY	SHIPPER_NAME	SHIPPING_PAPER_NUMBER	CARGO_TANK	HAZMAT_PLACARD_REQ	ASPEN_VERSION_NUMBER	SNET_VERSION_NUMBER	SNET_SEARCH_DATE	ALCOHOL_CONTROL_SUB	DRUG_INTRDCTN_SEARCH	DRUG_INTRDCTN_ARRESTS	SIZE_WEIGHT_ENF	TRAFFIC_ENF	LOCAL_ENF_JURISDICTION	INSP_CONFIDENCE_LEVEL	PEN_CEN_MATCH	PENFIELD2	PENFIELD3	FINAL_STATUS_DATE	POST_ACC_IND	GROSS_COMB_VEH_WT	DEFECT_VER	OOS_DEFECT_VER	VIOL_NOT_SENT	OOS_NOT_SENT	VIOL_TOTAL	OOS_TOTAL	DRIVER_VIOL_TOTAL	DRIVER_OOS_TOTAL	VEHICLE_VIOL_TOTAL	VEHICLE_OOS_TOTAL	HAZMAT_VIOL_TOTAL	HAZMAT_OOS_TOTAL	SNET_SEQUENCE_ID	ORIG_REPORT_STATE	ORIG_REPORT_NUMBER	ORIG_REPORT_DATE	ORIG_REPORT_TIME	TRANSACTION_CODE	TRANSACTION_DATE	UPLOAD_DATE	UPLOAD_FIRST_BYTE	UPLOAD_DOT_NUMBER	UPLOAD_SEARCH_INDICATOR	CENSUS_SEARCH_DATE	MCMIS_ADD_DATE	CHANGE_BY_USER	CHANGE_DATE	CHANGE_BY_APPL	SNET_INPUT_DATE	SOURCE_OFFICE"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11	v12	v13	v14	v15	v16	v17	v18	v19	v20	v21	v22	v23	v24	v25	v26	v27	v28	v29	v30	v31	v32	v33	v34	v35	v36	v37	v38	v39	v40	v41	v42	v43	v44	v45	v46	v47	v48	v49	v50	v51	v52	v53	v54	v55	v56	v57	v58	v59	v60	v61	v62	v63	v64	v65	v66	v67	v68	v69"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var 	INSPECTION_ID "INSPECTION ID "
	label var	DOT_NUMBER "USDOT NUMBER "
	label var	REPORT_STATE "REPORT STATE "
	label var	REPORT_NUMBER "REPORT NUMBER "
	label var	INSP_DATE "INSPECTION DATE "
	label var	INSP_START_TIME "START TIME "
	label var	INSP_END_TIME "END TIME "
	label var	REGISTRATION_DATE "REGISTRATION DATE "
	label var	REGION "REGION "
	label var	CI_STATUS_CODE "STATUS CODE "
	label var	SAFETY_INSPECTOR_KEY "SAFETY INSPECTOR KEY "
	label var	LOCATION "LOCATION "
	label var	LOCATION_DESC "LOCATION DESCRIPTION "
	label var	COUNTY_CODE_STATE "COUNTY CODE STATE "
	label var	COUNTY_CODE "COUNTY CODE "
	label var	INSP_LEVEL_ID "INSPECTION LEVEL ID "
	label var	SERVICE_CENTER "SERVICE CENTER "
	label var	CENSUS_SOURCE_ID "CENSUS SOURCE ID "
	label var	INSP_FACILITY "INSPECTION FACILITY "
	label var	SHIPPER_NAME "SHIPPER NAME "
	label var	SHIPPING_PAPER_NUMBER "SHIPPING PAPER NUMBER "
	label var	CARGO_TANK "CARGO TANK "
	*label var	**HAZMAT_INSP_TYPE "HAZMAT INSPECTION TYPE "
	label var	HAZMAT_PLACARD_REQ "HAZMAT PLACARD REQUIRED "
	label var	ASPEN_VERSION_NUMBER "ASPEN VERSION NUMBER "
	label var	SNET_VERSION_NUMBER "SNET VERSION NUMBER "
	label var	SNET_SEARCH_DATE "SNET SEARCH DATE "
	label var	ALCOHOL_CONTROL_SUB "ALCOHOL/CONTROLLED SUBSTANCE CHECK "
	label var	DRUG_INTRDCTN_SEARCH "DRUG INTERDICTION SEARCH "
	label var	DRUG_INTRDCTN_ARRESTS "DRUG INTERDICTION ARREST "
	label var	SIZE_WEIGHT_ENF "SIZE WEIGHT ENFORCEMENT "
	label var	TRAFFIC_ENF "TRAFFIC ENFORCEMENT "
	label var	LOCAL_ENF_JURISDICTION "LOCAL ENFORCEMENT JURISDICTION "
	label var	INSP_CONFIDENCE_LEVEL "INSPECTOR CONFIDENCE LEVEL "
	label var	PEN_CEN_MATCH "PEN CEN MATCH "
	label var	PENFIELD2 "PEN FIELD 2 "
	label var	PENFIELD3 "PEN FIELD 3 "
	label var	FINAL_STATUS_DATE "FINAL STATUS DATE "
	label var	POST_ACC_IND "POST ACCIDENT INDICATOR "
	label var	GROSS_COMB_VEH_WT "GROSS COMBINED VEHICLE WEIGHT "
	label var	DEFECT_VER "DEFECT VERIFICATION "
	label var	OOS_DEFECT_VER "OUT-OF-SERVICE DEFECT VERIFICATION "
	*label var	**TOTAL_HAZMAT_SENT "TOTAL HAZMAT SENT "
	label var	VIOL_NOT_SENT "VIOLATIONS NOT SENT "
	label var	OOS_NOT_SENT "OUT-OF-SERVICE NOT SENT "
	label var	VIOL_TOTAL "TOTAL VIOLATIONS "
	label var	OOS_TOTAL "TOTAL OUT-OF-SERVICE VIOLATIONS "
	label var	DRIVER_VIOL_TOTAL "TOTAL DRIVER VIOLATIONS "
	label var	DRIVER_OOS_TOTAL "TOTAL DRIVER OUT-OF-SERVICE "
	label var	VEHICLE_VIOL_TOTAL "TOTAL VEHICLE VIOLATIONS "
	label var	VEHICLE_OOS_TOTAL "TOTAL VEHICLE OUT-OF-SERVICE "
	label var	HAZMAT_VIOL_TOTAL "HAZMAT VIOLATIONS TOTAL "
	label var	HAZMAT_OOS_TOTAL "HAZMAT OUT-OF-SERVICE TOTAL "
	label var	SNET_SEQUENCE_ID "SAFETYNET SEQUENCE ID "
	label var	ORIG_REPORT_STATE "ORIGINAL REPORT STATE "
	label var	ORIG_REPORT_NUMBER "ORIGINAL REPORT NUMBER "
	label var	ORIG_REPORT_DATE "ORIGINAL INSPECTION DATE "
	label var	ORIG_REPORT_TIME "ORIGINAL START TIME "
	label var	TRANSACTION_CODE "TRANSACTION CODE "
	label var	TRANSACTION_DATE "TRANSACTION DATE "
	label var	UPLOAD_DATE "UPLOAD DATE "
	label var	UPLOAD_FIRST_BYTE "UPLOAD CENSUS FIRST BYTE "
	label var	UPLOAD_DOT_NUMBER "UPLOAD CENSUS NUMBER "
	label var	UPLOAD_SEARCH_INDICATOR "UPLOAD SEARCH INDICATOR "
	label var	CENSUS_SEARCH_DATE "CENSUS SEARCH DATE "
	label var	MCMIS_ADD_DATE "MCMIS ADD DATE "
	label var	CHANGE_BY_USER "CHANGE BY USER "
	label var	CHANGE_DATE "CHANGE DATE "
	label var	CHANGE_BY_APPL "CHANGE BY APPLICATION "
	label var	SNET_INPUT_DATE "SAFETYNET INPUT DATE "
	label var	SOURCE_OFFICE "SOURCE OFFICE "   
	}	
	
	
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}


****************
local t = "Insp_Carrier_Pub"	
		// inspection file in 2018
	import delimited "`inspfile'2018/`t'_01012018_10082018.txt", encoding(ISO-8859-1) clear	
	if `name_var' == 1{   // (12 variables)
	local insp_var "INSP_CARRIER_ID	INSPECTION_ID	INSP_CARRIER_NAME	INSP_CARRIER_STREET	INSP_CARRIER_CITY	INSP_CARRIER_STATE	INSP_CARRIER_ZIP_CODE	INSP_COLONIA	PREFIX	DOCKET_NUMBER	INSP_INTERSTATE	INSP_CARRIER_STATE_ID"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11	v12"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	
	label var	INSP_CARRIER_ID "CARRIER ID "
	label var	INSPECTION_ID "INSPECTION ID "
	label var	INSP_CARRIER_NAME "CARRIER NAME "
	label var	INSP_CARRIER_STREET "CARRIER STREET "
	label var	INSP_CARRIER_CITY "CARRIER CITY "
	label var	INSP_CARRIER_STATE "CARRIER STATE "
	label var	INSP_CARRIER_ZIP_CODE "CARRIER ZIP "
	label var	INSP_COLONIA "COLONIA "
	label var	PREFIX "PREFIX "
	label var	DOCKET_NUMBER "DOCKET NUMBER "
	label var	INSP_INTERSTATE "INTERSTATE "
	label var	INSP_CARRIER_STATE_ID "CARRIER STATE ID "  
	}		
	export delimited using "InspectionData/`t'_2018.csv", replace 
		// inspection file in all other years
	forvalues i = 2003/2017 {
	import delimited "`inspfile'`i'/`t'_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   
	local insp_var "INSP_CARRIER_ID	INSPECTION_ID	INSP_CARRIER_NAME	INSP_CARRIER_STREET	INSP_CARRIER_CITY	INSP_CARRIER_STATE	INSP_CARRIER_ZIP_CODE	INSP_COLONIA	PREFIX	DOCKET_NUMBER	INSP_INTERSTATE	INSP_CARRIER_STATE_ID"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11	v12"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	//  (12 variables, ** are not publicly available)
	label var	INSP_CARRIER_ID "CARRIER ID "
	label var	INSPECTION_ID "INSPECTION ID "
	label var	INSP_CARRIER_NAME "CARRIER NAME "
	label var	INSP_CARRIER_STREET "CARRIER STREET "
	label var	INSP_CARRIER_CITY "CARRIER CITY "
	label var	INSP_CARRIER_STATE "CARRIER STATE "
	label var	INSP_CARRIER_ZIP_CODE "CARRIER ZIP "
	label var	INSP_COLONIA "COLONIA "
	label var	PREFIX "PREFIX "
	label var	DOCKET_NUMBER "DOCKET NUMBER "
	label var	INSP_INTERSTATE "INTERSTATE "
	label var	INSP_CARRIER_STATE_ID "CARRIER STATE ID "  
	}		
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}
	forvalues i = 2000/2002 {  // variable names existed
	import delimited "`inspfile'`i'/`t'_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   // (12 variables)
	local insp_var "INSP_CARRIER_ID	INSPECTION_ID	INSP_CARRIER_NAME	INSP_CARRIER_STREET	INSP_CARRIER_CITY	INSP_CARRIER_STATE	INSP_CARRIER_ZIP_CODE	INSP_COLONIA	PREFIX	DOCKET_NUMBER	INSP_INTERSTATE	INSP_CARRIER_STATE_ID"
	local v "insp_carrier_id inspection_id insp_carrier_name insp_carrier_street insp_carrier_city insp_carrier_state insp_carrier_zip_code insp_colonia prefix docket_number insp_interstate insp_carrier_state_id"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  (12 variables, ** are not publicly available)
	label var	INSP_CARRIER_ID "CARRIER ID "
	label var	INSPECTION_ID "INSPECTION ID "
	label var	INSP_CARRIER_NAME "CARRIER NAME "
	label var	INSP_CARRIER_STREET "CARRIER STREET "
	label var	INSP_CARRIER_CITY "CARRIER CITY "
	label var	INSP_CARRIER_STATE "CARRIER STATE "
	label var	INSP_CARRIER_ZIP_CODE "CARRIER ZIP "
	label var	INSP_COLONIA "COLONIA "
	label var	PREFIX "PREFIX "
	label var	DOCKET_NUMBER "DOCKET NUMBER "
	label var	INSP_INTERSTATE "INTERSTATE "
	label var	INSP_CARRIER_STATE_ID "CARRIER STATE ID "  
	}		
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}
	foreach i of numlist 1989/1994 1996/1999 {
	import delimited "`inspfile'`i'/`t'_`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   
	local insp_var "INSP_CARRIER_ID	INSPECTION_ID	INSP_CARRIER_NAME	INSP_CARRIER_STREET	INSP_CARRIER_CITY	INSP_CARRIER_STATE	INSP_CARRIER_ZIP_CODE	INSP_COLONIA	PREFIX	DOCKET_NUMBER	INSP_INTERSTATE	INSP_CARRIER_STATE_ID"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11	v12"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	 // (12 variables, ** are not publicly available)
	label var	INSP_CARRIER_ID "CARRIER ID "
	label var	INSPECTION_ID "INSPECTION ID "
	label var	INSP_CARRIER_NAME "CARRIER NAME "
	label var	INSP_CARRIER_STREET "CARRIER STREET "
	label var	INSP_CARRIER_CITY "CARRIER CITY "
	label var	INSP_CARRIER_STATE "CARRIER STATE "
	label var	INSP_CARRIER_ZIP_CODE "CARRIER ZIP "
	label var	INSP_COLONIA "COLONIA "
	label var	PREFIX "PREFIX "
	label var	DOCKET_NUMBER "DOCKET NUMBER "
	label var	INSP_INTERSTATE "INTERSTATE "
	label var	INSP_CARRIER_STATE_ID "CARRIER STATE ID "  
	}		
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}

****************
local t = "Insp_Unit_Pub"
		// inspection file in 2018
	import delimited "`inspfile'2018/`t'_01012018_10082018.txt", encoding(ISO-8859-1) clear	
	if `name_var' == 1 {   // (11 variables)
	local insp_var "INSP_UNIT_ID	INSPECTION_ID	INSP_UNIT_TYPE_ID	INSP_UNIT_NUMBER	INSP_UNIT_MAKE	INSP_UNIT_COMPANY	INSP_UNIT_LICENSE	INSP_UNIT_LICENSE_STATE	INSP_UNIT_VEHICLE_ID_NUMBER	INSP_UNIT_DECAL	INSP_UNIT_DECAL_NUMBER"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	 
	label var	INSP_UNIT_ID "UNIT ID "
	label var	INSPECTION_ID "INSPECTION ID "
	label var	INSP_UNIT_TYPE_ID "UNIT TYPE "
	label var	INSP_UNIT_NUMBER "UNIT NUMBER "
	label var	INSP_UNIT_MAKE "UNIT MAKE "
	label var	INSP_UNIT_COMPANY "UNIT COMPANY "
	label var	INSP_UNIT_LICENSE "UNIT LICENSE "
	label var	INSP_UNIT_LICENSE_STATE "UNIT LICENSE STATE "
	label var	INSP_UNIT_VEHICLE_ID_NUMBER "UNIT VEHICLE ID NUMBER "
	label var	INSP_UNIT_DECAL "DECAL ISSUED "
	label var	INSP_UNIT_DECAL_NUMBER "UNIT DECAL NUMBER "  
	}		
	export delimited using "InspectionData/`t'_2018.csv", replace 
		// inspection file in all other years
	forvalues i = 2000/2017 {
	
	import delimited "`inspfile'`i'/`t'_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{  //  (11 variables)
	local insp_var "INSP_UNIT_ID	INSPECTION_ID	INSP_UNIT_TYPE_ID	INSP_UNIT_NUMBER	INSP_UNIT_MAKE	INSP_UNIT_COMPANY	INSP_UNIT_LICENSE	INSP_UNIT_LICENSE_STATE	INSP_UNIT_VEHICLE_ID_NUMBER	INSP_UNIT_DECAL	INSP_UNIT_DECAL_NUMBER"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	INSP_UNIT_ID "UNIT ID "
	label var	INSPECTION_ID "INSPECTION ID "
	label var	INSP_UNIT_TYPE_ID "UNIT TYPE "
	label var	INSP_UNIT_NUMBER "UNIT NUMBER "
	label var	INSP_UNIT_MAKE "UNIT MAKE "
	label var	INSP_UNIT_COMPANY "UNIT COMPANY "
	label var	INSP_UNIT_LICENSE "UNIT LICENSE "
	label var	INSP_UNIT_LICENSE_STATE "UNIT LICENSE STATE "
	label var	INSP_UNIT_VEHICLE_ID_NUMBER "UNIT VEHICLE ID NUMBER "
	label var	INSP_UNIT_DECAL "DECAL ISSUED "
	label var	INSP_UNIT_DECAL_NUMBER "UNIT DECAL NUMBER "  
	}		
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}
	foreach i of numlist 1989/1994 1996/1999 {
	import delimited "`inspfile'`i'/`t'_`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   // (11 variables)
	local insp_var "INSP_UNIT_ID	INSPECTION_ID	INSP_UNIT_TYPE_ID	INSP_UNIT_NUMBER	INSP_UNIT_MAKE	INSP_UNIT_COMPANY	INSP_UNIT_LICENSE	INSP_UNIT_LICENSE_STATE	INSP_UNIT_VEHICLE_ID_NUMBER	INSP_UNIT_DECAL	INSP_UNIT_DECAL_NUMBER"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	INSP_UNIT_ID "UNIT ID "
	label var	INSPECTION_ID "INSPECTION ID "
	label var	INSP_UNIT_TYPE_ID "UNIT TYPE "
	label var	INSP_UNIT_NUMBER "UNIT NUMBER "
	label var	INSP_UNIT_MAKE "UNIT MAKE "
	label var	INSP_UNIT_COMPANY "UNIT COMPANY "
	label var	INSP_UNIT_LICENSE "UNIT LICENSE "
	label var	INSP_UNIT_LICENSE_STATE "UNIT LICENSE STATE "
	label var	INSP_UNIT_VEHICLE_ID_NUMBER "UNIT VEHICLE ID NUMBER "
	label var	INSP_UNIT_DECAL "DECAL ISSUED "
	label var	INSP_UNIT_DECAL_NUMBER "UNIT DECAL NUMBER "  
	}		
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}


**************** 
local t = "Insp_Viol_Pub" // the variable sequence was changed from the one posted on FMCSA website
			// inspection file in 2018
	import delimited "`inspfile'2018/`t'_01012018_10082018.txt", encoding(ISO-8859-1) clear	
	if `name_var' == 1{   // (12 variables)
	local insp_var "INSP_VIOLATION_ID	INSPECTION_ID	SEQ_NO	PART_NO	PART_NO_SECTION	INSP_VIOL_UNIT	INSP_UNIT_ID	INSP_VIOLATION_CATEGORY_ID	OUT_OF_SERVICE_INDICATOR	DEFECT_VERIFICATION_ID	CITATION_NUMBER PART_SECTION_ID"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11 v12"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	
	label var	INSP_VIOLATION_ID	 "VIOLATION ID"
	label var	INSPECTION_ID	 "INSPECTION ID"
	label var	SEQ_NO	 "VIOLATION SEQUENCE NUMBER"
	label var	PART_NO	 "VIOLATION CODE PART NUMBER"
	label var	PART_NO_SECTION	 "VIOLATION CODE SECTION NUMBER"
	label var	INSP_VIOL_UNIT	 "VIOLATION UNIT"
	label var	INSP_UNIT_ID	 "UNIT ID"
	label var	INSP_VIOLATION_CATEGORY_ID	 "VIOLATION CATEGORY ID"
	label var	OUT_OF_SERVICE_INDICATOR	 "OUT-OF-SERVICE INDICATOR"
	label var	DEFECT_VERIFICATION_ID	 "DEFECT VERIFICATION ID"
	label var	CITATION_NUMBER	 "CITATION NUMBER" 
	label var	PART_SECTION_ID	 "VIOLATION SECTION ID"
	}		
	export delimited using "InspectionData/`t'_2018.csv", replace 
		// inspection file in all other years
	forvalues i = 2000/2017 {	
	import delimited "`inspfile'`i'/`t'_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{  //  (12 variables)
	local insp_var "INSP_VIOLATION_ID	INSPECTION_ID	SEQ_NO	PART_NO	PART_NO_SECTION	INSP_VIOL_UNIT	INSP_UNIT_ID	INSP_VIOLATION_CATEGORY_ID	OUT_OF_SERVICE_INDICATOR	DEFECT_VERIFICATION_ID	CITATION_NUMBER PART_SECTION_ID	"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11 v12"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	INSP_VIOLATION_ID	 "VIOLATION ID"
	label var	INSPECTION_ID	 "INSPECTION ID"
	label var	SEQ_NO	 "VIOLATION SEQUENCE NUMBER"
	label var	PART_NO	 "VIOLATION CODE PART NUMBER"
	label var	PART_NO_SECTION	 "VIOLATION CODE SECTION NUMBER"
	label var	INSP_VIOL_UNIT	 "VIOLATION UNIT"
	label var	INSP_UNIT_ID	 "UNIT ID"
	label var	INSP_VIOLATION_CATEGORY_ID	 "VIOLATION CATEGORY ID"
	label var	OUT_OF_SERVICE_INDICATOR	 "OUT-OF-SERVICE INDICATOR"
	label var	DEFECT_VERIFICATION_ID	 "DEFECT VERIFICATION ID"
	label var	CITATION_NUMBER	 "CITATION NUMBER" 
	label var	PART_SECTION_ID	 "VIOLATION SECTION ID"
	}		
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}
	foreach i of numlist 1989/1994 1996/1999 {	// 1995 is missing for now; file name is diff, no records for citation_number
	import delimited "`inspfile'`i'/`t'_`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   // (11 variables)
	local insp_var "INSP_VIOLATION_ID	INSPECTION_ID	SEQ_NO	PART_NO	PART_NO_SECTION	PART_SECTION_ID	INSP_VIOL_UNIT	INSP_UNIT_ID	INSP_VIOLATION_CATEGORY_ID	OUT_OF_SERVICE_INDICATOR	DEFECT_VERIFICATION_ID"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	INSP_VIOLATION_ID	 "VIOLATION ID"
	label var	INSPECTION_ID	 "INSPECTION ID"
	label var	SEQ_NO	 "VIOLATION SEQUENCE NUMBER"
	label var	PART_NO	 "VIOLATION CODE PART NUMBER"
	label var	PART_NO_SECTION	 "VIOLATION CODE SECTION NUMBER"
	label var	INSP_VIOL_UNIT	 "VIOLATION UNIT"
	label var	INSP_UNIT_ID	 "UNIT ID"
	label var	INSP_VIOLATION_CATEGORY_ID	 "VIOLATION CATEGORY ID"
	label var	OUT_OF_SERVICE_INDICATOR	 "OUT-OF-SERVICE INDICATOR"
	label var	DEFECT_VERIFICATION_ID	 "DEFECT VERIFICATION ID"
	label var	PART_SECTION_ID	 "VIOLATION SECTION ID"
	}		
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}

****************
local t = "Insp_ViolShip_Pub"   // 0 bytes in 1989-1994, 1995 is missing now
			// inspection file in 2018
	import delimited "`inspfile'2018/`t'_01012018_10082018.txt", encoding(ISO-8859-1) clear	
	if `name_var' == 1{   //  (12 variables)
	local insp_var "INSP_VIOL_SHIPPER_ID	INSP_VIOLATION_ID	SHIPPER_NAME	SHIPPER_STREET	SHIPPER_CITY	SHIPPER_STATE	SHIPPER_ZIP_CODE	SHIPPER_FIRST_BYTE	SHIPPER_DOT_NUMBER	PART_NO	PART_SECTION_ID	PART_NO_SECTION"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11 v12"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	
	label var	INSP_VIOL_SHIPPER_ID	"SHIPPER ID"
	label var	INSP_VIOLATION_ID	"INSPECTION VIOLATION ID"
	label var	SHIPPER_NAME	"SHIPPER NAME"
	label var	SHIPPER_STREET	"SHIPPER STREET ADDRESS"
	label var	SHIPPER_CITY	"SHIPPER CITY"
	label var	SHIPPER_STATE	"SHIPPER STATE"
	label var	SHIPPER_ZIP_CODE	"SHIPPER ZIP CODE"
	label var	SHIPPER_FIRST_BYTE	"SHIPPER CENSUS 1stBYTE"
	label var	SHIPPER_DOT_NUMBER	"SHIPPER USDOT NUMBER"
	label var	PART_NO	"SHIPPER VIOLATION CODE PART NUMBER"
	label var	PART_SECTION_ID	"VIOLATION SECTION ID"
	label var	PART_NO_SECTION	"SHIPPER VIOLATION CODE SECTION NUMBER" 
	}
	export delimited using "InspectionData/`t'_2018.csv", replace 
		// inspection file in all other years
	forvalues i = 2000/2017 {	
	import delimited "`inspfile'`i'/`t'_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{  
	local insp_var "INSP_VIOL_SHIPPER_ID	INSP_VIOLATION_ID	SHIPPER_NAME	SHIPPER_STREET	SHIPPER_CITY	SHIPPER_STATE	SHIPPER_ZIP_CODE	SHIPPER_FIRST_BYTE	SHIPPER_DOT_NUMBER	PART_NO	PART_SECTION_ID	PART_NO_SECTION"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11 v12"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	
	label var	INSP_VIOL_SHIPPER_ID	"SHIPPER ID"
	label var	INSP_VIOLATION_ID	"INSPECTION VIOLATION ID"
	label var	SHIPPER_NAME	"SHIPPER NAME"
	label var	SHIPPER_STREET	"SHIPPER STREET ADDRESS"
	label var	SHIPPER_CITY	"SHIPPER CITY"
	label var	SHIPPER_STATE	"SHIPPER STATE"
	label var	SHIPPER_ZIP_CODE	"SHIPPER ZIP CODE"
	label var	SHIPPER_FIRST_BYTE	"SHIPPER CENSUS 1stBYTE"
	label var	SHIPPER_DOT_NUMBER	"SHIPPER USDOT NUMBER"
	label var	PART_NO	"SHIPPER VIOLATION CODE PART NUMBER"
	label var	PART_SECTION_ID	"VIOLATION SECTION ID"
	label var	PART_NO_SECTION	"SHIPPER VIOLATION CODE SECTION NUMBER" 
	}
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}
	forvalues i = 1996/1999 {	
	import delimited "`inspfile'`i'/`t'_`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{  
	local insp_var "INSP_VIOL_SHIPPER_ID	INSP_VIOLATION_ID	SHIPPER_NAME	SHIPPER_STREET	SHIPPER_CITY	SHIPPER_STATE	SHIPPER_ZIP_CODE	SHIPPER_FIRST_BYTE	SHIPPER_DOT_NUMBER	PART_NO	PART_SECTION_ID	PART_NO_SECTION"
	local v "v1	v2	v3 	v4	v5	v6	v7	v8	v9	v10	v11 v12"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	INSP_VIOL_SHIPPER_ID	"SHIPPER ID"
	label var	INSP_VIOLATION_ID	"INSPECTION VIOLATION ID"
	label var	SHIPPER_NAME	"SHIPPER NAME"
	label var	SHIPPER_STREET	"SHIPPER STREET ADDRESS"
	label var	SHIPPER_CITY	"SHIPPER CITY"
	label var	SHIPPER_STATE	"SHIPPER STATE"
	label var	SHIPPER_ZIP_CODE	"SHIPPER ZIP CODE"
	label var	SHIPPER_FIRST_BYTE	"SHIPPER CENSUS 1stBYTE"
	label var	SHIPPER_DOT_NUMBER	"SHIPPER USDOT NUMBER"
	label var	PART_NO	"SHIPPER VIOLATION CODE PART NUMBER"
	label var	PART_SECTION_ID	"VIOLATION SECTION ID"
	label var	PART_NO_SECTION	"SHIPPER VIOLATION CODE SECTION NUMBER" 
	}
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}
****************** 
local t "Insp_Study_Pub"   // 0 bytes in 1989-1994
			// inspection file in 2018
	import delimited "`inspfile'2018/`t'_01012018_10082018.txt", encoding(ISO-8859-1) clear	
	if `name_var' == 1{   // (4 variables)
	local insp_var "INSP_STUDY_ID INSPECTION_ID STUDY SEQ_NO"
	local v "v1	v2	v3 	v4"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	
	label var	INSP_STUDY_ID	"STUDY ID"
	label var	INSPECTION_ID	"INSPECTION ID"
	label var	STUDY	"STUDY"
	label var	SEQ_NO	"SEQUENCE NUMBER" 
	}
	export delimited using "InspectionData/`t'_2018.csv", replace 
		// inspection file in all other years
	forvalues i = 2000/2017 {	
	import delimited "`inspfile'`i'/`t'_0101`i'_1231`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1 { 
	local insp_var "INSP_STUDY_ID INSPECTION_ID STUDY SEQ_NO"
	local v "v1	v2	v3 	v4"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	INSP_STUDY_ID	"STUDY ID"
	label var	INSPECTION_ID	"INSPECTION ID"
	label var	STUDY	"STUDY"
	label var	SEQ_NO	"SEQUENCE NUMBER" 
	}
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}
	forvalues i = 1996/1999 {	
	import delimited "`inspfile'`i'/`t'_`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1 { 
	local insp_var "INSP_STUDY_ID INSPECTION_ID STUDY SEQ_NO"
	local v "v1	v2	v3 	v4"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	INSP_STUDY_ID	"STUDY ID"
	label var	INSPECTION_ID	"INSPECTION ID"
	label var	STUDY	"STUDY"
	label var	SEQ_NO	"SEQUENCE NUMBER" 
	}
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}

*****************
local t "Insp_Supp_Violation"   // only avail in 2015-2018
			// inspection file in 2015-2018
	forvalues i = 2015/2018 {	
	import delimited "`inspfile'`i'/`t'`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{  //  (2 variables)
	local insp_var "INSP_VIOLATION_ID SUPP_DESC"
	local v "v1	v2"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	INSP_VIOLATION_ID "VIOLATION ID"
	label var	SUPP_DESC "VIOLATION DESCRIPTION"   
	}
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}
******************
local t "Insp_Part_Section"  // only avail in 2015-2018
		// inspection file in 2015-2018
	forvalues i = 2015/2018 {	
	import delimited "`inspfile'`i'/`t'`i'.txt", encoding(ISO-8859-1) clear
	if `name_var' == 1{   // (4 variables)
	local insp_var "PART_SECTION_ID	PART_NO	PART_NO_SECTION	FEDVIOCODE	DISPLAY_VALUE"
	local v "v1	v2	v3 	v4 v5"
	rename (`v') (`insp_var')	
	}	
	if `lab_var' == 1 {	  
	label var	PART_SECTION_ID "VIOLATION SECTION ID "
	label var	PART_NO "VIOLATION CODE PART NUMBER "
	label var	PART_NO_SECTION "VIOLATION CODE SECTION NUMBER "
	label var	FEDVIOCODE "VIOLATION CODE "
	label var	DISPLAY_VALUE "VIOLATION REGULATION " 
	}
	export delimited using "InspectionData/`t'_`i'.csv", replace
	}














